package com.kpmg.datalake.common.utils;

import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kpmg.datalake.common.exception.CustomException;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * Excel工具类
 * @author Tony
 */
public class ExcelUtils {
	
	private ExcelUtils() {}
	
	private static final Logger logger=LoggerFactory.getLogger(ExcelUtils.class);

	public static void listToExcel(List<Map<String,Object>> list, Map<String, String> fieldMap, String fileName,
			String sheetName, HttpServletRequest request, HttpServletResponse response) {
		listToExcel(list, fieldMap, fileName, sheetName, 65535, request, response);
	}
	
	private static final String EXPORT_ERROR_MESSAGE="导出Excel时异常，异常信息:{}";

	public static void listToExcel(List<Map<String,Object>> list, Map<String, String> fieldMap, String fileName,
			String sheetName, int sheetSize, HttpServletRequest request, HttpServletResponse response) {

		// 设置response头信息
		ResponseUtils.setExcelDownloadResponseHeader(fileName, request, response);

		// 创建工作簿并发送到浏览器
		try {

			OutputStream out = response.getOutputStream();
			listToExcel(list, fieldMap, sheetName, sheetSize, out);

		} catch (CustomException e) {
			logger.error(EXPORT_ERROR_MESSAGE,e.getMessage(),e);
			throw e;
		} catch (Exception e) {
			logger.error(EXPORT_ERROR_MESSAGE,e.getMessage(),e);
			throw new CustomException("导出Excel失败");
		}
	}

	public static void listToExcel(List<Map<String,Object>> list, Map<String, String> fieldMap, String sheetName,
			int sheetSize, OutputStream out) {

		if (list == null || list.isEmpty()) {
			throw new CustomException("数据源中没有任何数据");
		}
		
		int maxRow=65535;
		if (sheetSize > maxRow || sheetSize < 1) {
			sheetSize = maxRow;
		}

		// 创建工作簿并发送到OutputStream指定的地方
		WritableWorkbook wwb;
		try {
			wwb = Workbook.createWorkbook(out);

			// 因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条
			// 所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
			// 1.计算一共有多少个工作表
			double sheetNum = Math.ceil(list.size() / Double.valueOf(String.valueOf(sheetSize)));

			// 2.创建相应的工作表，并向其中填充数据
			for (int i = 0; i < sheetNum; i++) {
				// 如果只有一个工作表的情况
				if (1 == sheetNum) {
					WritableSheet sheet = wwb.createSheet(sheetName, i);
					fillSheet(sheet, list, fieldMap, 0, list.size() - 1);

					// 有多个工作表的情况
				} else {
					WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);

					// 获取开始索引和结束索引
					int firstIndex = i * sheetSize;
					int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1
							: (i + 1) * sheetSize - 1;
					// 填充工作表
					fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
				}
			}

			wwb.write();
			wwb.close();

		} catch (CustomException e) {
			logger.error(EXPORT_ERROR_MESSAGE,e.getMessage(),e);
			throw e;
		} catch (Exception e) {
			logger.error(EXPORT_ERROR_MESSAGE,e.getMessage(),e);
			throw new CustomException("导出Excel失败");
		}

	}

	/**
	 * @MethodName : setColumnAutoSize
	 * @Description : 设置工作表自动列宽和首行加粗
	 * @param ws
	 */
	private static void setColumnAutoSize(WritableSheet ws, int extraWith) {
		// 获取本列的最宽单元格的宽度
		for (int i = 0; i < ws.getColumns(); i++) {
			int colWith = 0;
			for (int j = 0; j < ws.getRows(); j++) {
				String content = ws.getCell(i, j).getContents();
				int cellWith = content.length();
				if (colWith < cellWith) {
					colWith = cellWith;
				}
			}
			// 设置单元格的宽度为最宽宽度+额外宽度
			ws.setColumnView(i, colWith + extraWith);
		}

	}

	/**
	 * @MethodName : fillSheet
	 * @Description : 向工作表中填充数据
	 * @param sheet
	 *            工作表
	 * @param list
	 *            数据源
	 * @param fieldMap
	 *            中英文字段对应关系的Map
	 * @param firstIndex
	 *            开始索引
	 * @param lastIndex
	 *            结束索引
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	private static void fillSheet(WritableSheet sheet, List<Map<String,Object>> list, Map<String, String> fieldMap,
			int firstIndex, int lastIndex) throws WriteException {

		// 定义存放英文字段名和中文字段名的数组
		String[] enFields = new String[fieldMap.size()];
		String[] cnFields = new String[fieldMap.size()];

		// 填充数组
		int count = 0;
		for (Entry<String, String> entry : fieldMap.entrySet()) {
			enFields[count] = entry.getKey();
			cnFields[count] = entry.getValue();
			count++;
		}
		// 填充表头
		int rowNo = 0;
		for (int i = 0; i < cnFields.length; i++) {
			Label label = new Label(i, rowNo, cnFields[i]);
			sheet.addCell(label);
		}
		rowNo++;

		// 填充内容
		for (int index = firstIndex; index <= lastIndex; index++) {
			// 获取单个对象
			Map<String,Object> item = list.get(index);
			for (int i = 0; i < enFields.length; i++) {
				Object objValue = item.get(enFields[i]);
				String fieldValue = objValue == null ? "" : objValue.toString();
				Label label = new Label(i, rowNo, fieldValue);
				sheet.addCell(label);
			}

			rowNo++;
		}

		// 设置自动列宽
		setColumnAutoSize(sheet, 5);
	}
}
